-- create database drrr CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

CREATE TABLE user (
  id integer NOT NULL PRIMARY KEY AUTO_INCREMENT,
  username varchar(16) NOT NULL,
  password varchar(32) NOT NULL,
  email varchar(32)
)
  GO

CREATE TABLE room (
  id integer NOT NULL PRIMARY KEY AUTO_INCREMENT,
  owner integer NOT NULL,
  createDate lastLoad DEFAULT now(),
  type int DEFAULT 0,
  roomname VARCHAR(100) not NULL,
  membernum INT default 0,
  FOREIGN KEY (owner)	REFERENCES user(id)
)
  GO

CREATE TABLE roomChatList (
  roomid int NOT NULL,
  userid int NOT NULL,
  time lastLoad DEFAULT now(),
  content text NOT NULL,
  PRIMARY KEY (roomid, userid, time),
  FOREIGN KEY (userid)  REFERENCES user (id),
  FOREIGN KEY (roomid)  REFERENCES room (id)
)
  GO

-- Insert Data
INSERT INTO user (id,username,password) VALUES (1,'zhang','123');